﻿using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using ToolUtil;
using zyconfig;

namespace zymain
{
    public partial class FrmTpksEx : Form
    {
        private string filePath = string.Empty;
        private BindingSource BookData;
        public string uid;
        public int gjm;
        private int kjm;
        private List<string> zcksList = new List<string>();
        private List<string> zrksList = new List<string>();
        private List<int> skList = new List<int>();
        private List<int> zcxnList = new List<int>();
        private List<int> zrxnList = new List<int>();
        public FrmTpksEx()
        {
            InitializeComponent();
        }

        private void gshBtn_Click(object sender, EventArgs e)
        {
            string sbt = this.sBox.Text;
            int index = this.ebtBox.SelectedIndex;
            string bt = this.ebtBox.Items[index].ToString();
            this.excelGridView.Columns[bt].HeaderText = sbt;
            this.excelGridView.Columns[bt].DataPropertyName = bt;
            this.ebtBox.Items[index] = sbt;
        }

        private void FrmTpksEx_Load(object sender, EventArgs e)
        {
            string formName = "FrmTool";
            Form fr = System.Windows.Forms.Application.OpenForms[formName];
            if (fr != null)
            {
                FrmTool f1 = (FrmTool)fr;
                //取到Form1的值是1
                this.uid = f1.UserID;
                this.gjm = f1.Gjm;
            }
            this.sBox.SelectedIndex = 0;
            this.AddSk();
            this.AddZcks();
            this.AddZrks();
        }
        private void AddSk()
        {
            this.skKay.Items.Clear();
            this.skKay.Items.Add("所有书库");
            this.skList.Clear();
            this.skList.Add(0);
            using (DbOperHandler _open = new Common().Doh())
            {
                _open.Reset();
                _open.SqlCmd = "select 库键码 as kjm,书目库名 as mc from 书目数据库定义 where 馆键码=" + this.gjm + " and (文献类型='图书' or 文献类型='期刊') order by 库键码";
                DataTable dt = _open.GetDataTable();
                foreach (DataRow drow in dt.Rows)
                {
                    this.skKay.Items.Add(drow["mc"].ToString().Trim());
                    this.skList.Add(Convert.ToInt32(drow["kjm"]));
                }
                this.skKay.SelectedIndex = 0;
            }
        }
        private void AddZcxn()
        {
            this.zcxnKay.Items.Clear();
            this.zcxnKay.Items.Add("所有虚拟库室");
            this.zcxnList.Clear();
            this.zcxnList.Add(0);
            using (DbOperHandler _open = new Common().Doh())
            {
                _open.Reset();
                _open.SqlCmd = "select 虚拟库室 as dz,库室名称 as mc from 虚拟库室 where 馆键码=" + this.gjm + " order by 配置优先级";
                DataTable dt = _open.GetDataTable();
                foreach (DataRow drow in dt.Rows)
                {
                    this.zcxnKay.Items.Add(drow["mc"].ToString().Trim());
                    this.zcxnList.Add(Convert.ToInt32(drow["dz"]));
                }
                this.zcxnKay.SelectedIndex = 0;
            }
        }
        private void AddZrxn()
        {
            this.zrxnKay.Items.Clear();
            this.zrxnList.Clear();
            using (DbOperHandler _open = new Common().Doh())
            {
                _open.Reset();
                _open.SqlCmd = "select 虚拟库室 as dz,库室名称 as mc from 虚拟库室 where 馆键码=" + this.gjm + " order by 配置优先级";
                DataTable dt = _open.GetDataTable();
                foreach (DataRow drow in dt.Rows)
                {
                    this.zrxnKay.Items.Add(drow["mc"].ToString().Trim());
                    this.zrxnList.Add(Convert.ToInt32(drow["dz"]));
                }
                this.zrxnKay.SelectedIndex = 0;
            }
        }
        private void AddZcks()
        {
            this.zcksKay.Items.Clear();
            this.zcksKay.Items.Add("所有库室");
            this.zcksList.Clear();
            this.zcksList.Add("ALL");
            using (DbOperHandler _open = new Common().Doh())
            {
                string kfk = string.Empty;
                if (this.kjm > 0)
                {
                    _open.Reset();
                    _open.ConditionExpress = "库键码=" + this.kjm;
                    string kfks = _open.GetField("书目数据库定义", "可用分配库室").ToString().Trim();
                    string[] kfs = kfks.Split(' ');
                    foreach (string str in kfs)
                    {
                        kfk += "'" + str + "',";
                    }
                    if (!string.IsNullOrWhiteSpace(kfk))
                        kfk = kfk.Substring(0, kfk.Length - 1);
                }
                _open.Reset();
                if(this.kjm>0)
                    _open.SqlCmd = "select 馆藏地址 as dz,部门名称 as mc,允许外借 as wj from 馆藏地址定义 where 馆键码=" + this.gjm + " and 馆藏地址 in(" + kfk + ") order by 序号";
                else
                    _open.SqlCmd = "select 馆藏地址 as dz,部门名称 as mc,允许外借 as wj from 馆藏地址定义 where 馆键码=" + this.gjm + " order by 序号";
                DataTable dt = _open.GetDataTable();
                foreach(DataRow drow in dt.Rows)
                {
                    this.zcksKay.Items.Add(drow["mc"].ToString().Trim());
                    this.zcksList.Add(drow["dz"].ToString().Trim());
                }
                this.zcksKay.SelectedIndex = 0;
                this.kjm = this.skList[0];
            }
        }
        private void AddZrks()
        {
            this.zrksKay.Items.Clear();
            this.zrksList.Clear();
            using (DbOperHandler _open = new Common().Doh())
            {
                string kfk = string.Empty;
                if (this.kjm > 0)
                {
                    _open.Reset();
                    _open.ConditionExpress = "库键码=" + this.kjm;
                    string kfks = _open.GetField("书目数据库定义", "可用分配库室").ToString().Trim();
                    string[] kfs = kfks.Split(' ');
                    foreach (string str in kfs)
                    {
                        kfk += "'" + str + "',";
                    }
                    if (!string.IsNullOrWhiteSpace(kfk))
                        kfk = kfk.Substring(0, kfk.Length - 1);
                }
                _open.Reset();
                if (this.kjm > 0)
                    _open.SqlCmd = "select 馆藏地址 as dz,部门名称 as mc,允许外借 as wj from 馆藏地址定义 where 馆键码=" + this.gjm + " and 馆藏地址 in(" + kfk + ") order by 序号";
                else
                    _open.SqlCmd = "select 馆藏地址 as dz,部门名称 as mc,允许外借 as wj from 馆藏地址定义 where 馆键码=" + this.gjm + " order by 序号";
                DataTable dt = _open.GetDataTable();
                foreach (DataRow drow in dt.Rows)
                {
                    this.zrksKay.Items.Add(drow["mc"].ToString().Trim());
                    this.zrksList.Add(drow["dz"].ToString().Trim());
                }
                if(dt.Rows.Count>0)
                    this.zrksKay.SelectedIndex = 0;
            }
        }
        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();
            FrmWait wait = new FrmWait();
            wait.Show();
            wait.label4.Text = "";
            Application.DoEvents();
            DataTable table = new DataTable();
            table = ReadFromExcel(filePath, listBox1.SelectedItem.ToString());
            if (table.Columns.Contains("标准编码"))
            {
                table.Columns["标准编码"].ColumnName = "ISBN";
            }
            if (table.Rows.Count > 0)
            {
                this.BookData = new BindingSource();
                this.BookData.DataSource = table;
                this.bindingNavigator1.BindingSource = this.BookData;
                this.excelGridView.DataSource = this.BookData;
                this.ebtBox.Items.Clear();
                for (int i = 0; i < this.excelGridView.ColumnCount; i++)
                {
                    this.ebtBox.Items.Add(this.excelGridView.Columns[i].HeaderText);
                }
                if (this.ebtBox.Items.Count > 0)
                    this.ebtBox.SelectedIndex = 0;
                for (int k = 0; k < this.excelGridView.Columns.Count; k++)
                {
                    string column = this.excelGridView.Columns[k].HeaderText;
                    switch (column)
                    {
                        case "书号":
                            this.excelGridView.Columns["书号"].HeaderText = "ISBN";
                            break;
                        case "标准编码":
                            this.excelGridView.Columns["标准编码"].HeaderText = "ISBN";
                            break;
                        case "isbn":
                            this.excelGridView.Columns["isbn"].HeaderText = "ISBN";
                            break;
                        case "条码":
                            this.excelGridView.Columns["条码"].HeaderText = "条形码";
                            break;
                    }
                }
                this.gshBtn.Enabled = true;
                this.qrtpBtn.Enabled = true;
                this.tpxnBtn.Enabled = true;
            }
            else
            {
                this.gshBtn.Enabled = false;
                this.qrtpBtn.Enabled = false;
                this.tpxnBtn.Enabled = false;
            }
            wait.Close();
            string time1 = watch.Elapsed.ToString();
            MessageBox.Show("导入：" + table.Rows.Count + "条，耗时：" + time1);
        }
        private DataTable ReadFromExcel(string excelpath, string listBox)
        {
            DataTable table = new DataTable();
            string sExt = System.IO.Path.GetExtension(excelpath);
            if (sExt == ".xlsx" || sExt == ".xls")
            {
                string error;
                table = OfficeTool.ReadOfficeExcel(excelpath, listBox, out error);
                if (table.Rows.Count == 0)
                {
                    if (string.IsNullOrEmpty(error) && !error.Equals("文件格式有误"))
                    {
                        table = OpenXMLHelper.ExcelWorksheetToDataTable(excelpath, listBox);
                    }
                }
                return table;
            }
            else
            {
                MessageBox.Show("文件格式有误");
                return null;
            }
        }

        private void excelBtn_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            //ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录
            ofd.Filter = "Excel文件| *.xlsx";
            ofd.RestoreDirectory = true;//记录上次打开的目录
            ofd.Multiselect = false; //不允许选中多个文件
            ofd.ValidateNames = true;     //文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
            ofd.CheckFileExists = true;  //验证路径有效性
            ofd.CheckPathExists = true; //验证文件有效性
            string strName = string.Empty;
            if (string.IsNullOrEmpty(filePath))
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    strName = ofd.FileName;
                    filePath = strName;
                }
            }
            else
            {
                if (MessageBox.Show("确认要清除当前文件，重新加载吗？", "询问", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
                {
                    filePath = "";
                    listBox1.Items.Clear();
                    if (ofd.ShowDialog() == DialogResult.OK)
                    {
                        strName = ofd.FileName;
                        filePath = strName;
                    }
                }
            }
            if (strName == "")
            {
                MessageBox.Show("没有选择Excel文件！无法进行数据导入");
                return;
            }
            ExcelListBox(strName);
            if (this.listBox1.Items.Count > 0)
            {
                this.listBox1.SelectedIndex = 0;
            }
        }

        private void ExcelListBox(string excelpath)
        {
            string error;
            DataTable table = OfficeTool.OfficeExcelListBox(excelpath, out error);
            if (table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    this.listBox1.Items.Add(table.Rows[i]["Table_Name"].ToString());
                }
            }
            else
            {
                if (!error.Equals("文件格式有误"))
                {
                    using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelpath, false))
                    {
                        WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                        IEnumerable<Sheet> sheets = workbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                        int m = 0;
                        foreach (OpenXmlElement s in sheets)
                        {
                            foreach (OpenXmlAttribute attr in s.GetAttributes())
                            {
                                if (m % 3 == 0)//因为attr.Value中包含工作表名,工作表序号和ID号
                                {
                                    this.listBox1.Items.Add(attr.Value);//把工作表名装到List中
                                }
                                m++;
                            }
                        }
                    }
                }
            }
        }

        private void qrtpBtn_Click(object sender, EventArgs e)
        {
            string sql = string.Empty;
            int zrIndex = this.zrksKay.SelectedIndex;
            int zcIndex = this.zcksKay.SelectedIndex;
            string zrks = this.zrksList[zrIndex];
            if (zcIndex > 0)
            {
                sql = "AND 馆藏地址='" + this.zcksList[zcIndex] + "' ";
            }
            bool dlhf = false,txmf = false,sshf=false;
            for (int k = 0; k < this.excelGridView.ColumnCount; k++)
            {
                string ht = this.excelGridView.Columns[k].HeaderText;
                switch (ht)
                {
                    case "登录号":
                        dlhf = true;
                        break;
                    case "条形码":
                        txmf = true;
                        break;
                    case "索书号":
                        sshf = true;
                        break;
                }
            }
            if (MessageBox.Show("确认根据EXCEL进行批量调配收藏库室？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                if (this.excelGridView.Rows.Count > 0)
                {
                    using (DbOperHandler _open = new Common().Doh())
                    {
                        FrmWait wait = new FrmWait();
                        wait.Show();
                        wait.ShowInTaskbar = false;
                        wait.Owner = this;
                        int m = 0;
                        for (int i = 0; i < this.excelGridView.Rows.Count; i++)
                        {
                            string sql1 = " 馆键码=" + this.gjm;
                            if (dlhf || this.excelGridView.Columns.Contains("登录号"))
                            {
                                string dlh = this.excelGridView.Rows[i].Cells.GetCellValueFromColumnHeader("登录号").ToString();
                                dlh = StringUtil.ToDBC(dlh).Replace(",", ";");
                                List<string> dlhs = StringUtil.getDlhs(dlh);
                                string tm = string.Empty;
                                foreach (string str in dlhs)
                                {
                                    tm += "'" + str + "',";
                                }
                                if (!string.IsNullOrEmpty(tm))
                                    tm = tm.Substring(0, tm.Length - 1);
                                sql1 += " AND 登录号 in(" + tm + ") ";
                            }
                            if (txmf || this.excelGridView.Columns.Contains("条形码"))
                            {
                                string txm = this.excelGridView.Rows[i].Cells.GetCellValueFromColumnHeader("条形码").ToString();
                                txm= StringUtil.ToDBC(txm).Replace(",", ";");
                                List<string> txms = StringUtil.getDlhs(txm);
                                string tm = string.Empty;
                                foreach (string str in txms)
                                {
                                    tm += "'" + str + "',";
                                }
                                if (!string.IsNullOrEmpty(tm))
                                    tm = tm.Substring(0, tm.Length - 1);
                                sql1 += " AND 条形码 in(" + tm + ") ";
                            }
                            if (sshf || this.excelGridView.Columns.Contains("索书号"))
                            {
                                string flh = this.excelGridView.Rows[i].Cells.GetCellValueFromColumnHeader("索书号").ToString();
                                sql1 += " AND 索书号='"+flh+"' ";
                            }
                            _open.Reset();
                            _open.ConditionExpress = sql1+sql;
                            _open.AddFieldItem("馆藏地址", zrks);
                            m += _open.Update("馆藏典藏库");
                            wait.label4.Text = "正在处理 " + (i + 1).ToString();
                            Application.DoEvents();
                        }
                        wait.Close();
                        MessageBox.Show("成功调配" + m.ToString());
                    }
                }
            }
        }

        private void skKay_SelectedIndexChanged(object sender, EventArgs e)
        {
            int index = this.skKay.SelectedIndex;
            this.kjm = this.skList[index];
            this.AddZcks();
            this.AddZrks();
            this.AddZcxn();
            this.AddZrxn();
        }

        private void exitBtn_Click(object sender, EventArgs e)
        {
            base.Close();
        }

        private void tpxnBtn_Click(object sender, EventArgs e)
        {
            string sql = string.Empty;
            int zrIndex = this.zrxnKay.SelectedIndex;
            int zcIndex = this.zcxnKay.SelectedIndex;
            int zrks = this.zrxnList[zrIndex];
            if (zcIndex > 0)
            {
                sql = "AND 虚拟库室=" + this.zcxnList[zcIndex] + " ";
            }
            bool dlhf = false, txmf = false, sshf = false;
            for (int k = 0; k < this.excelGridView.ColumnCount; k++)
            {
                string ht = this.excelGridView.Columns[k].HeaderText;
                switch (ht)
                {
                    case "登录号":
                        dlhf = true;
                        break;
                    case "条形码":
                        txmf = true;
                        break;
                    case "索书号":
                        sshf = true;
                        break;
                }
            }
            if (MessageBox.Show("确认根据EXCEL进行批量调配虚拟库室？", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                if (this.excelGridView.Rows.Count > 0)
                {
                    using (DbOperHandler _open = new Common().Doh())
                    {
                        FrmWait wait = new FrmWait();
                        wait.Show();
                        wait.ShowInTaskbar = false;
                        wait.Owner = this;
                        int m = 0;
                        for (int i = 0; i < this.excelGridView.Rows.Count; i++)
                        {
                            string sql1 = " 馆键码=" + this.gjm;
                            if (dlhf || this.excelGridView.Columns.Contains("登录号"))
                            {
                                string dlh = this.excelGridView.Rows[i].Cells.GetCellValueFromColumnHeader("登录号").ToString();
                                dlh = StringUtil.ToDBC(dlh).Replace(",", ";");
                                List<string> dlhs = StringUtil.getDlhs(dlh);
                                string tm = string.Empty;
                                foreach (string str in dlhs)
                                {
                                    tm += "'" + str + "',";
                                }
                                if (!string.IsNullOrEmpty(tm))
                                    tm = tm.Substring(0, tm.Length - 1);
                                sql1 += " AND 登录号 in(" + tm + ") ";
                            }
                            if (txmf || this.excelGridView.Columns.Contains("条形码"))
                            {
                                string txm = this.excelGridView.Rows[i].Cells.GetCellValueFromColumnHeader("条形码").ToString();
                                txm = StringUtil.ToDBC(txm).Replace(",", ";");
                                List<string> txms = StringUtil.getDlhs(txm);
                                string tm = string.Empty;
                                foreach (string str in txms)
                                {
                                    tm += "'" + str + "',";
                                }
                                if (!string.IsNullOrEmpty(tm))
                                    tm = tm.Substring(0, tm.Length - 1);
                                sql1 += " AND 条形码 in(" + tm + ") ";
                            }
                            if (sshf || this.excelGridView.Columns.Contains("索书号"))
                            {
                                string flh = this.excelGridView.Rows[i].Cells.GetCellValueFromColumnHeader("索书号").ToString();
                                sql1 += " AND 索书号='" + flh + "' ";
                            }
                            _open.Reset();
                            _open.ConditionExpress = sql1 + sql;
                            _open.AddFieldItem("虚拟库室", zrks);
                            m += _open.Update("馆藏典藏库");
                            wait.label4.Text = "正在处理 " + (i + 1).ToString();
                            Application.DoEvents();
                        }
                        wait.Close();
                        MessageBox.Show("成功调配虚拟库室 " + m.ToString());
                    }
                }
            }
        }
    }
}
